using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SQLite;

namespace GPSNav.NET
{
	public class SQLiteGpsDataAccess : IGpsDataAccess
	{


		private List<Int64> nodesIndex2 = new List<Int64>();
		private Dictionary<Int64, List<Int64>> nodesNeighborsIndex2 = new Dictionary<Int64, List<Int64>>();

		private string mapName;
		private string mapFilename;
		private GpsNodesCache nodesCache;
		private GpsWaysCache waysCache;

		private SQLiteConnection sqlConnection;



		/*public SQLiteGpsDataAccess(string mapName)
		{
			this.mapName = mapName;
			Initialize();
		}  */


		public SQLiteGpsDataAccess(string mapFilename)
		{
			this.mapFilename = mapFilename;
			Initialize();
		}



		private SQLiteCommand waysCommand1;
		private SQLiteCommand waysCommand2;
		private void Initialize()
		{
			nodesCache = new GpsNodesCache();
			waysCache = new GpsWaysCache();
			//sqlConnection = new SQLiteConnection(@"Data Source=G:\gps\gps.sqlite;Pooling=true;FailIfMissing=false");
			sqlConnection = new SQLiteConnection(String.Format(@"Data Source={0};Pooling=true;FailIfMissing=false", this.mapFilename));
			/*sqlCommand = new SQLiteCommand(sqlConnection);
			sqlCommand.CommandType = System.Data.CommandType.Text;*/
			sqlConnection.Open();

			//LoadNodesIndex();
			//InitializeStreams(mapName);
			//getBounds();

			waysCommand1 = new SQLiteCommand(sqlConnection);
			waysCommand1.CommandType = System.Data.CommandType.Text;
			waysCommand1.CommandText = @"SELECT w.wayId, w.lod, wb.latStart, wb.lonStart, wb.latEnd, wb.lonEnd, wd.wayType, wd.waySubType, wd.wayString FROM ways w LEFT JOIN waysBounds wb ON w.wayId=wb.wayId LEFT JOIN waysData wd on w.wayId=wd.wayId WHERE w.wayId=@wayId";
			waysCommand1.Parameters.Add(new SQLiteParameter("@wayId", System.Data.DbType.Int64));
			waysCommand1.Prepare();


			waysCommand2 = new SQLiteCommand(sqlConnection);
			waysCommand2.CommandType = System.Data.CommandType.Text;
			waysCommand2.CommandText = @"SELECT wn.nodeId,n.lat,n.lon FROM waysNodes wn LEFT JOIN nodes n ON wn.nodeId=n.nodeId WHERE wayId=@wayId ORDER BY wn.nodeOrder";
			waysCommand2.Parameters.Add(new SQLiteParameter("@wayId", System.Data.DbType.Int64));
			waysCommand2.Prepare();



		}


		public GeoRectangle GetBounds()
		{
			GeoRectangle ret = GeoRectangle.Empty;
			SQLiteCommand sqlCommand = new SQLiteCommand(sqlConnection);
			sqlCommand.CommandText = "SELECT min(lat), max(lat), min(lon), max(lon) FROM nodes";
			SQLiteDataReader sdr = sqlCommand.ExecuteReader();
			while (sdr.Read())
			{
				ret = new GeoRectangle(new GeoPoint(sdr.GetDouble(0), sdr.GetDouble(2)), new GeoPoint(sdr.GetDouble(1), sdr.GetDouble(3)));
			}
			sdr.Close();
			sdr.Dispose();
			Console.WriteLine(ret);
			return ret;
		}

		private void LoadNodesIndex()
		{
			SQLiteCommand sqlCommand = new SQLiteCommand(sqlConnection);
			sqlCommand.CommandText = "SELECT nodeId FROM nodes";
			SQLiteDataReader sdr = sqlCommand.ExecuteReader();
			while (sdr.Read())
			{
				nodesIndex2.Add(sdr.GetInt64(0));
			}
			sdr.Close();
			sdr.Dispose();

			sqlCommand = new SQLiteCommand(sqlConnection);
			sqlCommand.CommandText = "SELECT nodeId,neighborId FROM neighbors";
			sdr = sqlCommand.ExecuteReader();
			while (sdr.Read())
			{
				Int64 nodeId = sdr.GetInt64(0);
				if (!nodesNeighborsIndex2.ContainsKey(nodeId))
				{
					nodesNeighborsIndex2.Add(nodeId, new List<Int64>());
				}
				nodesNeighborsIndex2 [nodeId].Add(sdr.GetInt64(1));
			}
			sdr.Close();
			sdr.Dispose();
		}

		public GpsNode GetNode(Int64 nodeId)
		{
			if (nodesCache.ContainsKey(nodeId))
			{
				return nodesCache [nodeId];
			}
			else
			{
				SQLiteCommand sqlCommand = new SQLiteCommand(sqlConnection);
				sqlCommand.CommandText = "SELECT lat,lon FROM nodes WHERE nodeId=" + nodeId.ToString();
				SQLiteDataReader sdr = sqlCommand.ExecuteReader();
				while (sdr.Read())
				{
					GpsNode ret = new GpsNode(nodeId, sdr.GetDouble(0), sdr.GetDouble(1));
					nodesCache.Add(nodeId, ret);
					return ret;

				}
				sdr.Close();
				sdr.Dispose();
			}
			return null;
		}

		public GpsNodesCollection GetNeighbors(Int64 nodeId)
		{

			GpsNodesCollection ret = new GpsNodesCollection();
			SQLiteCommand sqlCommand = new SQLiteCommand(sqlConnection);
			sqlCommand.CommandText = "SELECT neighborId FROM neighbors WHERE nodeId=" + nodeId.ToString();
			SQLiteDataReader sdr = sqlCommand.ExecuteReader();
			while (sdr.Read())
			{
				ret.Add(GetNode(sdr.GetInt64(0)));
			}
			sdr.Close();
			sdr.Dispose();
			return ret;
		}

		public GpsNodesCollection GetNodes(GeoRectangle rect)
		{
			return GetNodes(rect, 1);
		}

		public GpsNodesCollection GetNodes(GeoRectangle rect, int lod)
		{

			GpsNodesCollection ret = new GpsNodesCollection();
			GpsNode n = null;

			SQLiteCommand sqlCommand = new SQLiteCommand(sqlConnection);
			sqlCommand.CommandText = String.Format("SELECT nodeId FROM nodes WHERE lat>={0} AND lat<={1} AND lon>={2} AND lon<={3} and lod={4}", rect.Location.Lat, rect.Location.Lon, rect.Location.Lat + rect.Size.Width, rect.Location.Lon + rect.Size.Height, lod);
			SQLiteDataReader sdr = sqlCommand.ExecuteReader();
			while (sdr.Read())
			{

				Int64 cNodeId = sdr.GetInt64(0);
				if (nodesCache.ContainsKey(cNodeId))
				{
					ret.Add(nodesCache [cNodeId]);
				}
				else
				{
					ret.Add(GetNode(cNodeId));
				}


			}
			sdr.Close();
			sdr.Dispose();

			return ret;
		}



		public GpsWay GetWay(Int64 wayId)
		{

			if (waysCache.ContainsKey(wayId))
			{
				return waysCache [wayId];
			}
			else
			{

				GpsWay ret = null;
				/*SQLiteCommand sqlCommand = new SQLiteCommand(sqlConnection);
				sqlCommand.CommandText = String.Format("SELECT w.wayId, w.lod, wb.latStart, wb.lonStart, wb.latEnd, wb.lonEnd, wd.wayType, wd.waySubType, wd.wayString FROM ways w LEFT JOIN waysBounds wb ON w.wayId=wb.wayId LEFT JOIN waysData wd on w.wayId=wd.wayId WHERE w.wayId={0}", wayId);
				SQLiteDataReader sdr = sqlCommand.ExecuteReader();*/

				waysCommand1.Parameters [0].Value = wayId;
				SQLiteDataReader sdr = waysCommand1.ExecuteReader();

				if (sdr.Read())
				{
					int wayLod = sdr.GetInt32(1);
					GeoRectangle wayBounds = new GeoRectangle(sdr.GetDouble(2), sdr.GetDouble(3), sdr.GetDouble(4), sdr.GetDouble(5));
					int wayType = sdr.GetInt32(6);
					int waySubType = sdr.GetInt32(7);
					string wayString = sdr.GetString(8);
					ret = new GpsWay(wayId, wayLod, wayBounds, wayType, waySubType, wayString);

				}

				sdr.Close();
				sdr.Dispose();

				/*sqlCommand.CommandText = String.Format("SELECT wn.nodeId,n.lat,n.lon FROM waysNodes wn LEFT JOIN nodes n ON wn.nodeId=n.nodeId WHERE wayId={0} ORDER BY wn.nodeOrder", wayId);
				sdr = sqlCommand.ExecuteReader();*/

				waysCommand2.Parameters [0].Value = wayId;
				sdr = waysCommand2.ExecuteReader();

				while (sdr.Read())
				{
					//create nodes here for speed; dont rely on getnode
					Int64 nodeId = sdr.GetInt64(0);
					GeoPoint nodeLoc = new GeoPoint(sdr.GetDouble(1), sdr.GetDouble(2));
					if (!nodesCache.ContainsKey(nodeId))
					{
						nodesCache.Add(nodeId, new GpsNode(nodeId, nodeLoc));
					}
					ret.Nodes.Add(GetNode(nodeId));
				}
				sdr.Close();
				sdr.Dispose();

				//Console.WriteLine(tmpGpsWay);
				waysCache.Add(wayId, ret);
				return ret;
			}

			return null;

		}


		public GpsWaysCollection GetWays(GeoRectangle rect)
		{
			return GetWays(rect, 0, "ALL");
		}
		public GpsWaysCollection GetWays(GeoRectangle rect, int lod)
		{
			return GetWays(rect, lod, "=");
		}

		private GpsWaysSqlCache gpsWaysSqlCache = new GpsWaysSqlCache();
		public GpsWaysCollection GetWays(GeoRectangle rect, int lod, string lodOperator)
		{

			string lodSql = "";
			if (lodOperator == "ALL")
			{


			}
			else
			{
				lodSql = string.Format("w.lod{0}{1} AND", lodOperator, lod);
			}
			//"SELECT w.wayId  FROM ways w LEFT JOIN waysBounds wb ON w.wayId=wb.wayId WHERE w.lod={0} AND wb.latStart>={1} AND wb.lonStart>={2} AND wb.latEnd<={3} AND wb.lonEnd<={4}",			
			SQLiteCommand sqlCommand = new SQLiteCommand(sqlConnection);
			sqlCommand.CommandText = String.Format(
@"SELECT
w.wayId,
wd.wayType,wd.waySubType,wd.wayString,
wb.latStart,wb.lonStart,wb.latEnd,wb.lonEnd,
wn.nodeId,
n.lat,
n.lon,
w.lod

FROM ways w 
LEFT JOIN waysBounds wb 
	ON w.wayId=wb.wayId 
LEFT JOIN waysData wd 
	ON w.wayId=wd.wayId 
LEFT JOIN waysNodes wn
	ON w.wayId=wn.wayId
LEFT JOIN nodes n 
	ON wn.nodeId=n.nodeId

WHERE {0} 
(
	({1}<=wb.latStart AND {2}<=wb.lonStart AND {3}>=wb.latEnd AND {4}>=wb.lonEnd) OR 
	({1}>=wb.latStart AND {1}<=wb.latEnd) OR ({3}>=wb.latStart AND {3}<=wb.latEnd) OR
	({2}>=wb.lonStart AND {2}<=wb.lonEnd) OR ({4}>=wb.lonStart AND {4}<=wb.lonEnd)
)
ORDER BY w.lod,w.wayId,wn.nodeOrder
",
lodSql, rect.Location.Lat, rect.Location.Lon, rect.LocationEnd.Lat, rect.LocationEnd.Lon,
lodOperator
);


			if (gpsWaysSqlCache.ContainsKey(sqlCommand.CommandText))
			{
				return gpsWaysSqlCache [sqlCommand.CommandText].gpsWaysCollection;
			}
			else
			{
				foreach (KeyValuePair<string,GpsWaysSqlCacheResult> c in gpsWaysSqlCache)
				{
					if (c.Value.lod == lod && c.Value.lodOperator == lodOperator && c.Value.rect.Contains(rect))
					{
						return c.Value.gpsWaysCollection;
					}
				}
			}


			Console.WriteLine(sqlCommand.CommandText);
			//SQLiteTransaction t = sqlConnection.BeginTransaction();
			SQLiteDataReader sdr = sqlCommand.ExecuteReader();
			List<Int64> tmp = new List<long>();

			GpsWaysCollection ret = new GpsWaysCollection();

			Int64 wayId = -1;
			Int64 pwayId = -1;
			GpsWay tmpGpsWay = null;
			while (sdr.Read())
			{
				pwayId = wayId;
				wayId = sdr.GetInt64(0);
				if (pwayId != wayId)
				{
					//new way
					if (waysCache.ContainsKey(wayId))
					{
						ret.Add(waysCache [wayId]);
						tmpGpsWay = null;
						continue;
					}
					else
					{

						int wayLod = sdr.GetInt32(11);
						//Console.WriteLine(lod);
						GeoRectangle wayBounds = new GeoRectangle(sdr.GetDouble(4), sdr.GetDouble(5), sdr.GetDouble(6), sdr.GetDouble(7));
						int wayType = sdr.GetInt32(1);
						int waySubType = sdr.GetInt32(2);
						string wayString = sdr.GetString(3);
						tmpGpsWay = new GpsWay(wayId, wayLod, wayBounds, wayType, waySubType, wayString);
						waysCache.Add(wayId, tmpGpsWay);
						ret.Add(waysCache [wayId]);

					}
				}

				if (tmpGpsWay != null)
				{

					Int64 nodeId = sdr.GetInt64(8);
					if (!nodesCache.ContainsKey(nodeId))
					{
						GpsNode n = new GpsNode(nodeId, sdr.GetDouble(9), sdr.GetDouble(10));
						nodesCache.Add(nodeId, n);
					}

					tmpGpsWay.Nodes.Add(nodesCache [nodeId]);

				}
			}
			//t.Commit();

			gpsWaysSqlCache.Add(sqlCommand.CommandText, 
				new GpsWaysSqlCacheResult(sqlCommand.CommandText,rect,lod,lodOperator,ret)
				);
			return gpsWaysSqlCache [sqlCommand.CommandText].gpsWaysCollection;
			//return ret;
		}





		#region IDisposable Members

		public void Dispose()
		{
			((IDisposable) this).Dispose();
		}

		#endregion

		#region IDisposable Members

		void IDisposable.Dispose()
		{
		}

		#endregion



		#region IGpsDataAccess Members


		public GpsNode GetClosestNode(GeoPoint location)
		{
			throw new Exception("The method or operation is not implemented.");



		}

		#endregion
	}


}
